package edu.yuhf.databasedemo;

import edu.yuhf.domain.Subject;
import edu.yuhf.vo.Student;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.IntStream;

public class DatabaseOperation {
    private static Logger logger = Logger.getLogger(DatabaseOperation.class);
    //最简单的JDBC操作
    public void jdbcExampleSimple(){
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");  //JDBC4。0以后的版本，可以省略，但是为了兼容性，还是建议加上
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_school", "root", "123456");
            ResultSet resultSet = connection.createStatement().executeQuery("select * from subject");
            while (resultSet.next()) {
                System.out.println(resultSet.getObject("id")+" "+resultSet.getString("subject_name")+" "+resultSet.getObject("credit"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //使用Statement执行SQL语句，返回一个表的所有数据
    public <T> List<T> JdbcExapleForTable(ResultSetHandler<T> resultSetHandler){
        Connection connection = null;
        List<T> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_school", "root", "123456");
            ResultSet resultSet = connection.createStatement().executeQuery("select * from subject");
            while (resultSet.next()) {
                T subject = resultSetHandler.handler(resultSet);  //使用策略模式，将结果集的处理交给调用者
                list.add(subject);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
    //使用Statement执行SQL语句，返回复集合，一般用于多表查询
    public List<Map<String,Object>> jdbcExampleForMutilTable(){
        Connection connection = null;
        var url ="select si.id,si.sno,si.name,c.college_name from student_info si left outer join college c on si.college_id=c.id";
        List<Map<String,Object>> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_school", "root", "123456");
            ResultSet resultSet = connection.createStatement().executeQuery(url);
            while (resultSet.next()) {
                Map<String,Object> map = new HashMap<>();
                ResultSetMetaData rsmd = resultSet.getMetaData();
                IntStream.range(1, rsmd.getColumnCount()).forEach(i->{
                    try {
                        map.put(rsmd.getColumnName(i),resultSet.getObject(i));
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                });
                list.add(map);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
    public int jdbcExampleForUpdate(){
        int count = 0;
        Connection connection = null;
        var url ="update student_info set college_id=1 where id=4";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_school", "root", "123456");
            count = connection.createStatement().executeUpdate(url);
        } catch (ClassNotFoundException |SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }
    public int jdbcExampleForInsert(){
        int count = 0;
        Connection connection = null;
        var sql ="insert into student_info(sno,name,college_id,birthday,sex) values(?,?,?,?,?)";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_school", "root", "123456");
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"sno007");
            preparedStatement.setString(2,"喜洋洋");
            preparedStatement.setInt(3,1);
            preparedStatement.setDate(4, Date.valueOf("2025-6-6"));
            preparedStatement.setString(5,"1");
            count = preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }
    public List<Student> jdbcExampleForStudentSelect(String sql, Object...params){
        List<Student> list = new ArrayList<>();
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_school", "root", "123456");
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ParameterMetaData pmd = preparedStatement.getParameterMetaData();
            if(null!=params && params.length>0 && params.length==pmd.getParameterCount()){
                IntStream.iterate(0,i -> i+1).limit(params.length).forEach(i->{
                    try {
                        preparedStatement.setObject(i+1,params[i]);
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                });
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setSno(resultSet.getString("sno"));
                student.setName(resultSet.getString("name"));
                student.setCollegeId(resultSet.getInt("college_id"));
                student.setBirthday(resultSet.getDate("birthday"));
                student.setSex(resultSet.getString("sex"));
                list.add(student);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
}
